/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apt.Bean.LectureAuthorEntities;
import org.apt.connectDB.MyConnect;

/**
 *
 * @author Administrator
 */
public class LectureAuthorDao {

    public static List<LectureAuthorEntities> getLectureAuthor(int pageno) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<LectureAuthorEntities> listLectureAuthor = new ArrayList<LectureAuthorEntities>();
        int pageno2;
        try {
            if (pageno <= 1) {
                pageno = 1;
            } else if (pageno == 2) {
                pageno = 10 + 1;
            } else if (pageno > 2) {
                pageno = (pageno - 1) * 10 + 1;
            }
            pageno2 = pageno + 9;
            con = MyConnect.getConnection();
            st = con.createStatement();
            String sql = "SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ID) AS row,* FROM LectureAuthor)  as tblTemp WHERE  row between " + pageno + " AND " + pageno2 + " ";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                LectureAuthorEntities lectureAuthor = new LectureAuthorEntities();
                lectureAuthor.setID(rs.getInt("ID"));
                lectureAuthor.setLectureID(rs.getInt("LectureID"));
                lectureAuthor.setFullName(rs.getString("FullName"));
                lectureAuthor.setIDCard(rs.getInt("IDCard"));
                lectureAuthor.setImage(rs.getString("Image"));
                lectureAuthor.setEmail(rs.getString("Email"));
                lectureAuthor.setDOB(rs.getString("DOB"));
                lectureAuthor.setMobilePhone(rs.getInt("MobilePhone"));
                lectureAuthor.setAddress(rs.getString("Address"));
                lectureAuthor.setStatus(rs.getBoolean("Status"));
                lectureAuthor.setDescription(rs.getString("Description"));
                listLectureAuthor.add(lectureAuthor);
            }
        } catch (SQLException ex) {
            Logger.getLogger(LectureAuthorDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return listLectureAuthor;
    }

    /**
     * function get LectureAuthor by ID
     * @param lectureID
     * @return
     */
    public static LectureAuthorEntities getLectureAuthorByLectureID(int lectureID) {
        LectureAuthorEntities lectureAuthor = new LectureAuthorEntities();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String sql = "select * from LectureAuthor where LectureID = " + lectureID + "";
            con = MyConnect.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);

            while (rs.next()) {
                lectureAuthor.setID(rs.getInt("ID"));
                lectureAuthor.setLectureID(rs.getInt("LectureID"));
                lectureAuthor.setFullName(rs.getString("FullName"));
                lectureAuthor.setIDCard(rs.getInt("IDCard"));
                lectureAuthor.setImage(rs.getString("Image"));
                lectureAuthor.setEmail(rs.getString("Email"));
                lectureAuthor.setDOB(rs.getString("DOB"));
                lectureAuthor.setMobilePhone(rs.getInt("MobilePhone"));
                lectureAuthor.setAddress(rs.getString("Address"));
                lectureAuthor.setStatus(rs.getBoolean("Status"));
                lectureAuthor.setDescription(rs.getString("Description"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(MemberDao.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return lectureAuthor;
    }


    public static void main(String[] args) {
        List<LectureAuthorEntities> a = getLectureAuthor(1);
        System.out.println(a.size());
    }
}
